package birs.controller.staff.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.staff.STF003Model;
import birs.model.staff.STF003ModelA;
import birs.model.staff.STF003ModelB;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class STF003Task {
	
	Connection con = null;
	
	public STF003Task(Connection con){
		this.con = con;
	}
	
	public void getStaffReward(HttpServletRequest request,HttpServletResponse response) throws IOException, SQLException {
		ResultSetMapper<STF003Model> staffReward = new ResultSetMapper<STF003Model>();
		List<STF003Model> staffRewardModelList = staffReward.mapRersultSetToObject(getMainReward(), STF003Model.class);

		// List<ST003ModelA> st003List = new ArrayList<ST003ModelA>();
		// ST003ModelA st003ModelA = new ST003ModelA();
		// List<ST003Model> staffRwArray = new ArrayList<ST003Model>();
		// for (int i=0; i<staffRewardModelList.size(); i++) {
		// if(i==0){
		// st003ModelA.setByteCode(staffRewardModelList.get(i).getByteCode());
		// st003ModelA.setByteDes(staffRewardModelList.get(i).getBytedes());
		// staffRwArray.add(staffRewardModelList.get(i));
		// }else{
		// if(!staffRewardModelList.get(i).getByteCode().equals(staffRewardModelList.get(i-1).getByteCode())){
		// st003ModelA.setSt003Model(staffRwArray);
		// st003List.add(st003ModelA);
		//
		// st003ModelA = new ST003ModelA();//create new instant
		// st003ModelA.setByteCode(staffRewardModelList.get(i).getByteCode());
		// st003ModelA.setByteDes(staffRewardModelList.get(i).getBytedes());
		//
		// staffRwArray = new ArrayList<ST003Model>();//create new instant
		// staffRwArray.add(staffRewardModelList.get(i));
		// }else
		// if(staffRewardModelList.get(i).getByteCode().equals(staffRewardModelList.get(i-1).getByteCode())){
		// staffRwArray.add(staffRewardModelList.get(i));
		// }
		//
		// if(i == staffRewardModelList.size()-1){
		// st003ModelA.setSt003Model(staffRwArray);
		// st003List.add(st003ModelA);
		// }
		// }
		// }
		// String resultJson =
		// ConvertDataType.getInstance().objectToJasonArray(st003List);

		String resultJson = ConvertDataType.getInstance().objectToJasonArray(staffRewardModelList);
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	public void getDrilldownByReward(HttpServletRequest request,HttpServletResponse response,String p1) throws IOException, SQLException {
		ResultSetMapper<STF003ModelA> resultSetMapper = new ResultSetMapper<STF003ModelA>();
		List<STF003ModelA> st003ModelAList = resultSetMapper.mapRersultSetToObject(getDrilldownByRewardSQL(p1), STF003ModelA.class);
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(st003ModelAList);
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	public void getSecondDrilldown(HttpServletRequest request,HttpServletResponse response,String p1) throws IOException, SQLException {
		ResultSetMapper<STF003ModelB> resultSetMapper = new ResultSetMapper<STF003ModelB>();
		List<STF003ModelB> st003ModelBList = resultSetMapper.mapRersultSetToObject(getSecondDrilldownSql(p1), STF003ModelB.class);
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(st003ModelBList);
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getDrilldownByRewardSQL(String rewardId) throws SQLException{
		String sql = "SELECT ROWNUM, SR.STAFFID, P.PREFIXNAME || S.STAFFNAME || ' ' ||S.STAFFSURNAME AS STAFFNAME, "
				+ "SR.RECEIVEDATE "
				+ "FROM STAFFREWARD SR, STAFF S, PREFIX P  "
				+ "WHERE SR.REWARDID = "+rewardId
				+ "AND SR.REWARDSTATUS = 1 "
				+ "AND SR.REWARDSTATUS=1 AND SR.STAFFID = S.STAFFID "
				+ "AND S.PREFIXID = P.PREFIXID ";
		return getData(sql);
	}

	private ResultSet getMainReward() throws SQLException {
		String sql = "SELECT SB.BYTECODE, SB.BYTEDES,RW.REWARDID,RW.REWARDNAME,COUNT(RW.REWARDNAME) AS AMOUNT "
				+ "FROM PROMIS.REWARD RW, PROMIS.STAFFREWARD SR, PROMIS.SYSBYTEDES  SB "
				+ "WHERE SB.TABLENAME = 'REWARD' "
				+ "AND SB.COLUMNNAME = 'REWARDTYPE' "
				+ "AND SB.BYTECODE=RW.REWARDTYPE "
				+ "AND SR.REWARDID = RW.REWARDID "
				+ "AND SR.REWARDSTATUS = '1' "
				+ "GROUP BY SB.BYTECODE,SB.BYTEDES,RW.REWARDID,RW.REWARDNAME "
				+ "ORDER BY SB.BYTECODE";

		return getData(sql);
	}
	
	private ResultSet getSecondDrilldownSql(String staffid) throws SQLException{
		String sql = "SELECT ROWNUM, STAFFID,STAFFNAME,REWARDNAME,RECEIVEDATE "
				+ " FROM ( "
				+ " SELECT SR.STAFFID,P.PREFIXNAME || S.STAFFNAME || ' ' ||S.STAFFSURNAME AS STAFFNAME, "
				+ " SR.RECEIVEDATE, RW.REWARDNAME "
				+ " FROM STAFFREWARD SR, REWARD RW, STAFF S, PREFIX P "
				+ " WHERE SR.STAFFID = "+staffid
				+ " AND SR.REWARDID = RW.REWARDID "
				+ " AND SR.STAFFID   = S.STAFFID "
				+ " AND S.PREFIXID   = P.PREFIXID "
				+ " ORDER BY SR.RECEIVEDATE) ";
		return getData(sql);
	}

	private ResultSet getData(String sql) throws SQLException {
		ResultSet result = null;
		try {
			/*ConnectionDB.getInstance();
			con = ConnectionDB.getMisConnection();*/
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}
